﻿using System;
using System.Data;
using System.Collections.Generic;
using HsTec.Mobile.Green.Model;
using HsTec.Mobile.Green.Util;
using System.Data.SqlClient;
namespace HsTec.Mobile.Green.BLL
{
    /// <summary>
    /// 业务逻辑类Benchmark 的摘要说明。
    /// </summary>
    public class BenchmarkBLL
    {
        private readonly HsTec.Mobile.Green.DAL.BenchmarkDAL dal = new HsTec.Mobile.Green.DAL.BenchmarkDAL();
        public BenchmarkBLL()
        { }
        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string BM_IDs)
        {
            return dal.Exists(BM_IDs);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(HsTec.Mobile.Green.Model.Benchmark model)
        {
            return dal.Add(model);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(HsTec.Mobile.Green.Model.Benchmark model)
        {
            return dal.Update(model);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(string BM_IDs)
        {

            return dal.Delete(BM_IDs);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public HsTec.Mobile.Green.Model.Benchmark GetModel(int BM_ID)
        {

            return dal.GetModel(BM_ID);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataView GetList(string strWhere)
        {
            return dal.GetList(strWhere);
        }
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataView GetList(int Top, string strWhere, string filedOrder)
        {
            return dal.GetList(Top, strWhere, filedOrder);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<HsTec.Mobile.Green.Model.Benchmark> GetModelList(string strWhere)
        {
            DataView ds = dal.GetList(strWhere);
            return DataTableToList(ds.Table);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<HsTec.Mobile.Green.Model.Benchmark> DataTableToList(DataTable dt)
        {
            List<HsTec.Mobile.Green.Model.Benchmark> modelList = new List<HsTec.Mobile.Green.Model.Benchmark>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                HsTec.Mobile.Green.Model.Benchmark model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new HsTec.Mobile.Green.Model.Benchmark();
                    if (dt.Rows[n]["BM_ID"].ToString() != "")
                    {
                        model.BM_ID = int.Parse(dt.Rows[n]["BM_ID"].ToString());
                    }
                    model.TotalProportion = dt.Rows[n]["TotalProportion"].ToString();
                    model.LastMonthlProportion = dt.Rows[n]["LastMonthlProportion"].ToString();
                    model.CumulativProportion = dt.Rows[n]["CumulativProportion"].ToString();
                    model.AdjustProportion = dt.Rows[n]["AdjustProportion"].ToString();
                    model.ExpectedRate = dt.Rows[n]["ExpectedRate"].ToString();
                    model.ActualRate = dt.Rows[n]["ActualRate"].ToString();
                    model.CalculationBenchmark = dt.Rows[n]["CalculationBenchmark"].ToString();
                    model.ManuallyBenchmark = dt.Rows[n]["ManuallyBenchmark"].ToString();
                    modelList.Add(model);
                }
            }
            return modelList;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataView GetAllList()
        {
            return GetList("");
        }

        #endregion  成员方法

        public DataView GetBenchmarkList(string years, ref string sErr)
        {
            return dal.GetBenchmarkList(years, ref sErr);
        }

        public DataView GetAreaList()
        {
            return SQLHelper.ExecuteDataView("select * from Area where ParentID=10");
        }

        /// <summary>
        /// 负责保存标杆值
        /// </summary>
        /// <param name="dc"></param>
        /// <returns></returns>
        public int SetBenchMark(Dictionary<string, string> dc, string sYear)
        {
            SQLHelper.myconn.Open();
            SqlCommand command = SQLHelper.myconn.CreateCommand();
            SqlTransaction transaction;
            transaction = SQLHelper.myconn.BeginTransaction();
            command.Connection = SQLHelper.myconn;
            command.Transaction = transaction;
            try
            {
                foreach (string key in dc.Keys)
                {
                    command.CommandText = "delete from tb_ForecastBusinessNew where Years='" + sYear + "' and Area_ID='" + key + "'";
                    command.ExecuteNonQuery();
                    command.CommandText = "insert into tb_ForecastBusinessNew(Years,Area_ID,MarkValue) values('" + sYear + "','" + key + "','" + dc[key] + "')";
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
                return 1;
            }
            catch
            {
                transaction.Rollback();
                return 0;
            }
            finally
            {
                transaction.Dispose();
                SQLHelper.myconn.Close();
            }
        }

        public DataView GetItemsList()
        {
            return SQLHelper.ExecuteDataView("select FieldName,FieldAlias from tb_ReportTemplate where FieldType=0");
        }

        public DataView GetReportList(string sArea, string sYear, string sItems)
        {
            DataTable dt = new DataTable("dt_Query");
            dt.Columns.Add(new DataColumn("年月", typeof(string)));
            dt.Columns.Add(new DataColumn("地区", typeof(string)));
            string[] sAry = sItems.Split(',');
            for (int i = 0; i < sAry.Length; i++)
            {
                string[] tmp = sAry[i].Split('^');
                dt.Columns.Add(new DataColumn(tmp[1], typeof(string)));
            }
            string[] areaAry = sArea.Split(',');
            string[] yearAry = sYear.Split(',');
            int iNumber = 1;
            for (int i = 0; i < areaAry.Length; i++)
            {
                string[] aAry = areaAry[i].Split('^');
                for (int j = 0; j < yearAry.Length; j++)
                {
                    DataRow dr = dt.NewRow();
                    dr["年月"] = yearAry[j];
                    dr["地区"] = aAry[1];
                    for (int k = 0; k < sAry.Length; k++)
                    {
                        string[] tmp = sAry[k].Split('^');
                        string[] ary = tmp[0].Split('|');
                        //注意 ary[0] 是表名 ary[1] 是字段
                        dr[tmp[1]] = SQLHelper.ExecuteString("select " + ary[1] + " from " + ary[0] + " where Area_ID='" + aAry[0] + "' and Years='" + yearAry[j] + "'");
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt.DefaultView;
        }

        public DataView GetBenchmarkListB(string years, ref string sErr)
        {
            return dal.GetBenchmarkListB(years, ref sErr);
        }

        public string MaxUserID()
        {
            return SQLHelper.ExecuteString("select max(ID) from Sys_User_Info");
        }

        public int UpdateUserArea(string userid, string areaid)
        {
            return SQLHelper.ExecuteNonQuery("update Sys_User_Info set AreaId='" + areaid + "' where ID='" + userid + "'");
        }

        public string GetReportAreaID(string userid)
        {
            return SQLHelper.ExecuteString("select AreaId from Sys_User_Info where ID='" + userid + "'");
        }
    }
}

